﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using Lead2012.Models;
using Lead2012.ViewModels;
using Lead2012.Helpers;
using Lead2012.Business;
using System.Text.RegularExpressions;

namespace Lead2012.Repository
{
    public class LeadImportManagementRepository : BaseManagementRepository
    {
        // Create an instance of entities framework
        Lead2012Entities entities = new Lead2012Entities();

        public List<FieldInfo> GetAllFieldSample()
        {
            List<FieldInfo> list = new List<FieldInfo>();
            var listTab = new FieldManagementRepository().GetFieldGroup();

            foreach (FieldGroupInfo fgi in listTab)
            {
                if ((fgi.Fields != null) && (fgi.Fields.Count > 0))
                {
                    foreach (FieldInfo fi in fgi.Fields)
                    {
                        if (!fi.Visibility.ToUpper().Equals("INVISIBLE") && fi.TypeId != 32 && fi.TypeId != 64 && fi.TypeId != 1024)
                        {
                            list.Add(fi);
                        }
                    }
                }
                if ((fgi.Groups != null) && (fgi.Groups.Count > 0))
                {
                    foreach (FieldGroupInfo section in fgi.Groups)
                    {
                        if ((section != null) && (section.Fields != null))
                        {
                            foreach (FieldInfo fi in section.Fields)
                            {
                                if (!fi.Visibility.ToUpper().Equals("INVISIBLE") && fi.TypeId != 32 && fi.TypeId != 64 && fi.TypeId != 1024)
                                {
                                    list.Add(fi);
                                }
                            }
                        }
                    }
                }
            }

            return list;
        }

        // Save file
        // Check field mapping
        public LeadImportViewModel GetMappingModel(string filePath)
        {
            LeadImportViewModel model = new LeadImportViewModel();
            MappingModel map = new MappingModel();
            List<ItemFilter> list = new List<ItemFilter>();
            int i = 0;
            
            // Read all columns on upload file
            DataTable dt  = GetDataTable(filePath);
            if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
            {
                for (i= 0; i < dt.Columns.Count; i++)
                {
                    list.Add(new ItemFilter() { ID = (i + 1).ToString(), ItemName = dt.Columns[i].ToString() });
                }
            }
            model.Mapping = GetMappingModel(list);

            return model;
        }

        public LeadImportViewModel GetMappedWithDataModel(string filePath, List<ColumnModel> mappingSettings)
        {
            LeadImportViewModel model = new LeadImportViewModel();
            model = GetMappedModel(filePath, mappingSettings);
            return model;
        }

        public void AddNewLead(List<ColumnHeaderModel> columns, RowModel rows)
        {
            columns = columns.Where(c => c.IsIgnored == false).ToList();

            if (columns != null && columns.Count > 0)
            {
                List<FieldNameValue> listFieldValues = new List<FieldNameValue>();
                var cell = rows.Cells.Where(c => c.IsIgnored == false).ToList();

                for(int i=0;i<columns.Count;i++)
                {
                    listFieldValues.Add(new FieldNameValue(){FieldId = columns[i].FieldId, ColumnName = columns[i].ColumnName, ColumnValue = cell[i].Value});
                }

                int value = Create(0, 1, 1, 0, 0, 1, listFieldValues);
            }

            return;
        }

        #region Private methods
        private int Create(int userId, int statusId, int sourceId, int milestoneId, int lastActionTypeId, int leadFormTypeId, List<FieldNameValue> listFieldValues)
        {
            // First: Get all column names
            int value = 0;
            var columns = GetAllColumnName();

            // Second: Make an insert clause to add a new lead
            string sqlQuery = string.Empty;
            sqlQuery = "INSERT INTO [LeadWarehouse] ";
            sqlQuery += string.Format(" VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, N'{8}', N'{9}', N'{10}'", userId, statusId, sourceId, milestoneId, 0, 0, lastActionTypeId, leadFormTypeId, DateTime.Now, DateTime.Now, DateTime.Now);

            for (int i = 12; i < columns.Count; i++)
            {
                int fieldId = int.Parse(columns[i].Replace("f", string.Empty));
                var field = listFieldValues.Where(c => c.FieldId == fieldId).FirstOrDefault();
                if (field != null)
                {
                    sqlQuery += string.Format(", N'{0}'", field.ColumnValue);
                }
                else
                {
                    sqlQuery += string.Format(", N'{0}'", string.Empty);
                }
            }
            sqlQuery += ")";

            // Execute none query to insert data
            value = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlQuery);

            return value;
        }

        private MappingModel GetMappingModel(List<ItemFilter> list)
        {
            MappingModel model = new MappingModel();

            var listTab = new FieldManagementRepository().GetFieldGroup();
            var listFields = new FieldManagementRepository().GetAllFieldInfo();
            model.Columns = GetAllColumnHeaders(list, listFields, listTab);
            model.AllFieldGroups = GetAllFieldGroups(listTab);

            return model;
        }

        private LeadImportViewModel GetMappedModel(string filePath, List<ColumnModel> mappingSettings)
        {
            LeadImportViewModel model = new LeadImportViewModel();
            DataErrorModel data = new DataErrorModel();

            // 01. Get data from file upload
            var dataUpload = GetDataTable(filePath);

            // 02. Add columns mapped
            List<ColumnHeaderModel> cols = new List<ColumnHeaderModel>();
            foreach (var item in mappingSettings)
            {
                ColumnHeaderModel colModel = new ColumnHeaderModel();
                if (item.MappedField != null)
                {
                    colModel.FieldId = item.MappedField.FieldId;
                    colModel.Type = item.MappedField.TypeId;
                    colModel.RegularExpressions = item.MappedField.ClientExpression;
                }

                colModel.ColumnName = item.ColumnName;
                colModel.IsIgnored = item.IsIgnored;
                colModel.IsRequired = false;
                cols.Add(colModel);
            }
            data.ColumnHeaders = cols;

            // 03. Add data grid
            List<RowModel> list = new List<RowModel>();
            int indexing = 0;
            for (int i = 0; i < dataUpload.Rows.Count; i++)
            {
                RowModel rowModel = new RowModel();

                rowModel.RowNumber = (i + 1);
                //rowModel.IgnoreWhenImport = mappingSettings[i].IsIgnored;
                rowModel.Cells = GetDataRow(indexing, mappingSettings, dataUpload.Rows[i]);

                list.Add(rowModel);

                indexing += dataUpload.Columns.Count;
            }
            data.ErrorRows = list;
            model.DataError = data;

            return model;
        }

        private List<CellModel> GetDataRow(int indexing, List<ColumnModel> fields, DataRow rows)
        {
            List<CellModel> list = new List<CellModel>();

            for (int i = 0; i < rows.ItemArray.Count(); i++)
            {
                CellModel cell = new CellModel();

                cell.CellID = indexing + i;
                cell.Type = 1;
                
                if (fields[i] != null && fields[i].MappedField != null && fields[i].MappedField.IsRequired)
                {
                    cell.Type = fields[i].MappedField.TypeId;
                    cell.Value = rows[i].ToString();
                    cell.IgnoreValidation = true;

                    // Check with regular expression here
                    Match match = Regex.Match(rows[i].ToString(), cell.Value, RegexOptions.IgnoreCase);
                    if (match.Success)
                    {
                        cell.IsDone = true;
                        cell.IsError = false;
                    }
                    else
                    {
                        cell.IsDone = false;
                        cell.IsError = true;
                    }
                    if (string.IsNullOrEmpty(cell.Value))
                    {
                        cell.IsDone = false;
                        cell.IsError = true;
                    }
                }
                else
                {
                    cell.Value = rows[i].ToString();
                    cell.IgnoreValidation = true;
                    cell.IsDone = true;
                    cell.IsError = false;
                }

                list.Add(cell);
            }

            return list;
        }

        // Map columns of upload file with column exist on system
        // Calculate to return all fields that contains ignored or not
        private List<ColumnModel> GetAllColumnHeaders(List<ItemFilter> columns, List<FieldInfo> listFields, List<FieldGroupInfo> listGroupFields)
        {
            List<ColumnModel> list = new List<ColumnModel>();
            var expressionList = GetAllRegularExpression();

            foreach (var col in columns)
            {
                ColumnModel model = new ColumnModel();
                FieldModel mapField = new FieldModel();

                var fieldInfo = listFields.Where(c=>c.Title.Equals(col.ItemName, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                model.ColumnName = col.ItemName;

                if (fieldInfo != null)
                {
                    mapField.TypeId = fieldInfo.TypeId;
                    mapField.FieldId = fieldInfo.Id;
                    mapField.FieldName = fieldInfo.Title;
                    mapField.IsUsed = (fieldInfo.Visibility != "NO") ? true : false;
                    mapField.IsRequired = fieldInfo.Required;
                    mapField.Expression = expressionList.Where(c => c.TypeId == fieldInfo.TypeId).First().Expression;
                    mapField.ClientExpression = expressionList.Where(c => c.TypeId == fieldInfo.TypeId).First().ClientExpression;
                }
                else
                {
                    mapField = null;
                }

                model.PossibleGroups = GetAllFieldGroups(listGroupFields);
                model.PossibleFieldsInOrder = GetAllFieldsPossibleMatch(listFields, model.ColumnName);
                model.MappedField = mapField;

                list.Add(model);
            }
            
            return list;
        }
        
        // Get all group field on system
        // Return to math header columns on right panel
        private List<GroupModel> GetAllFieldGroups(List<FieldGroupInfo> listTab)
        {
            List<GroupModel> list = new List<GroupModel>();

            foreach (var group in listTab)
            {
                GroupModel model = new GroupModel();

                model.Title = group.Title;
                model.Fields = GetAllFields(group.Fields);

                // add sections
                List<SectionModel> listSection=new List<SectionModel>();
                foreach (var section in group.Groups)
                {
                    SectionModel sectionModel = new SectionModel();
                    sectionModel.Title = section.Title;
                    sectionModel.Fields = GetAllFields(section.Fields);
                    listSection.Add(sectionModel);
                }
                model.Sections=listSection;

                list.Add(model);
            }

            return list;
        }

        // Get all fields by group
        private List<FieldModel> GetAllFields(List<FieldInfo> listFields)
        {
            List<FieldModel> list = new List<FieldModel>();

            foreach (var item in listFields)
            {
                FieldModel field = new FieldModel();
                field.FieldId = item.Id;
                field.FieldName = item.Title;
                field.IsUsed = item.Visibility.Equals("NO") ? false : true;
                list.Add(field);
            }

            return list;
        }
        
        private List<FieldModel> GetAllFieldsPossibleMatch(List<FieldInfo> listFields, string colName)
        {
            List<FieldModel> list = new List<FieldModel>();

            foreach (var item in listFields)
            {
                if (item.Title.Contains(colName))
                {
                    FieldModel field = new FieldModel();
                    field.FieldId = item.Id;
                    field.FieldName = item.Title;
                    field.IsUsed = item.Visibility.Equals("NO") ? false : true;
                    list.Add(field);
                }
            }

            return list;
        } 
        
        // Mapped Conversion
        #endregion
    }
}